package com.carmaintain.db;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringEscapeUtils;

import net.sf.json.JSON;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import com.carmaintain.common.Constants;
import com.carmaintain.common.Utils;
import com.carmaintain.config.Configs;
import com.carmaintain.models.Customer;
import com.carmaintain.models.ItemShortcut;
import com.carmaintain.models.MaintainHistory;
import com.carmaintain.models.MaintainItem;
import com.carmaintain.models.MerchantPlace;
import com.carmaintain.models.Message;
import com.carmaintain.models.PlaceRequest;
import com.carmaintain.models.Request;
import com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException;

public class MerchantLib {	
	/**
	 * 添加经营场所
	 */
	public static boolean addPlace(String userid, String placeName, String linkMan, String officeTel,
			String address, String gpsLocation, String roadNearby, String mobile, String businessItem) throws Exception{
		int result;
		
		String sql = "" +
				" INSERT INTO" +
				"	" + Constants.TABLE_MERCHANT_PLACE + 
				"	(merchant_id, place_name, address, gps_location, road_nearby, linkman, office_phone, mobile, business_item, status) " +
				" VALUES " +
				"	('" + StringEscapeUtils.escapeSql(userid) + "', " +
				"	'" + StringEscapeUtils.escapeSql(placeName) + "', " + 
				"	'" + StringEscapeUtils.escapeSql(address) + "', " + 
				"	'" + StringEscapeUtils.escapeSql(gpsLocation) + "', " + 
				"	'" + StringEscapeUtils.escapeSql(roadNearby) + "', " + 
				"	'" + StringEscapeUtils.escapeSql(linkMan) + "', " + 
				"	'" + StringEscapeUtils.escapeSql(officeTel) + "', " + 
				"	'" + StringEscapeUtils.escapeSql(mobile) + "', " + 
				"	'" + StringEscapeUtils.escapeSql(businessItem) + "', " + 
				Constants.PLACE_STATUS_UNVERIFIED + ")";
		result = DBConn.update(sql);
		
		return (result > 0);
	}

	/**
	 * 添加合作经营场所
	 */
	public static boolean addPlaceCoop(String userid, String[] placeIds) throws Exception{
		String sql = "";
		
		for (int i = 0; i < placeIds.length; i++) {
			if (Utils.empty(placeIds[i])) {continue;}
			
			sql = "" +
					" INSERT INTO" +
					"	" + Constants.TABLE_MERCHANT_PLACE_COOP + 
					"	(merchant_id, place_id, status) " +
					" VALUES " +
					"	('" + StringEscapeUtils.escapeSql(userid) + "', " +
					"	'" + StringEscapeUtils.escapeSql(placeIds[i]) + "', " + 
					Constants.COOP_PLACE_STATUS_UNREQUEST + ")";
			DBConn.update(sql);
		}
		
		return true;
	}
	
	/**
	 * 修改经营场所
	 */
	public static int modifyPlace(String id, String merchantId, String placeName, String linkMan, String officeTel,
			String address, String gpsLocation, String roadNearby, String mobile, String businessItem, int type) throws Exception{
		int result;
		
		String sql = "" +
				" UPDATE " +
				"	" + Constants.TABLE_MERCHANT_PLACE + 
				" SET place_name = " + " '" + StringEscapeUtils.escapeSql(placeName) + "', " + 
				" 	address = " + " '" + StringEscapeUtils.escapeSql(address) + "', " + 
				" 	gps_location = " + " '" + StringEscapeUtils.escapeSql(gpsLocation) + "', " + 
				" 	road_nearby = " + " '" + StringEscapeUtils.escapeSql(roadNearby) + "', " + 
				" 	linkman = " + " '" + StringEscapeUtils.escapeSql(linkMan) + "', " + 
				" 	office_phone = " + " '" + StringEscapeUtils.escapeSql(officeTel) + "', " + 
				" 	mobile = " + " '" + StringEscapeUtils.escapeSql(mobile) + "', " + 
				" 	business_item = " + " '" + StringEscapeUtils.escapeSql(businessItem) + "', " +
				" 	status = " + Constants.PLACE_STATUS_UNVERIFIED +
				" WHERE id = " + StringEscapeUtils.escapeSql(id) + 
				" AND merchant_id = '" + StringEscapeUtils.escapeSql(merchantId) + "'"; 
		result = DBConn.update(sql);
		
		if (result >= 1) {
			return Constants.CODE_SUCCESS;
		} else {
			return Constants.CODE_FAIL;
		}
	}
	
	/**
	 * 添加客户
	 */
	public static int addCustomer(String merchantId, String licenseNumber, String carModel, String displacement, 
			String buyDate, String photo, String username, String mobile) throws Exception{
		
		String sql = "" +
				" INSERT INTO" +
				"	" + Constants.TABLE_CAR_INFO + 
				"	(license_number, model, displacement, buy_date, photo) " +
				" VALUES " +
				"	('" + StringEscapeUtils.escapeSql(licenseNumber) + "', " +
				"	'" + StringEscapeUtils.escapeSql(carModel) + "', " + 
				"	'" + StringEscapeUtils.escapeSql(displacement) + "', " + 
				"	'" + StringEscapeUtils.escapeSql(buyDate) + "', " + 
				"	'" + StringEscapeUtils.escapeSql(photo) +  "')";
		try {
			DBConn.update(sql);
		} catch (MySQLIntegrityConstraintViolationException e) {
			return Constants.CODE_LICENSE_EXIST;
		}
		
		sql = "" +
				" INSERT INTO" +
				"	" + Constants.TABLE_MAINTAIN_ORDER + 
				"	(order_id, license_number, merchant_id, place_id, customer_name, customer_mobile, mileage, maintain_date, maintain_fee, comment, status) " +
				" VALUES " +
				"	('" + Utils.getStrDate(System.currentTimeMillis()) + System.currentTimeMillis() + "', " +
				"	'" + StringEscapeUtils.escapeSql(licenseNumber) + "', " + 
				"	'" + StringEscapeUtils.escapeSql(merchantId) + "', " + 
				"	'0', " + 
				"	'" + StringEscapeUtils.escapeSql(username) + "', " + 
				"	'" + StringEscapeUtils.escapeSql(mobile) + "', " + 
				"	'0', " + 
				"	'" + Utils.getDateYMD(System.currentTimeMillis()) + "', " + 
				"	'0.00', " + 
				"	'', " + 
				"	'" + Constants.MAINTAIN_STATUS_WAIT +  "')";
		DBConn.update(sql);
		
		return Constants.CODE_SUCCESS;
	}
	
	/**
	 * 取得经营场所列表
	 */
	public static Map<String, Object> getPlaces(String userid, int type) throws Exception{
		Map<String, Object> dataMap = new HashMap<String, Object>();
		List<MerchantPlace> ret = new ArrayList<MerchantPlace>();
		MerchantPlace place = null;
		ResultSet result;
		String sql = "";
		int totalNum = 0;
		
		if (type == Constants.PLACE_TYPE_NORMAL || type == Constants.PLACE_TYPE_ALL) {
			sql = "" +
					" 	SELECT " +
					"		id, place_name, address, road_nearby, gps_location, linkman, office_phone, mobile, business_item, 0 as type, status" +
					" 	FROM " + Constants.TABLE_MERCHANT_PLACE + 
					" 	WHERE " +
					"		merchant_id = '" + StringEscapeUtils.escapeSql(userid) + "' ";
			result = DBConn.query(sql);
			
			if (result.first()) {
				do {
					place = new MerchantPlace();
					place.setId(result.getString("id"));
					place.setPlaceName(result.getString("place_name"));
					place.setAddress(result.getString("address"));
					place.setNearby(result.getString("road_nearby"));
					place.setGpsLocation(result.getString("gps_location"));
					place.setLinkMan(result.getString("linkman"));
					place.setOfficePhone(result.getString("office_phone"));
					place.setMobile(result.getString("mobile"));
					place.setBusinessItem(result.getString("business_item"));
					place.setType(result.getString("type"));
					place.setStatus(result.getString("status"));
					
					ret.add(place);
				} while (result.next());
			}
			
			sql = "" +
					" 	SELECT " +
					"		count(1) as total_num " +
					" 	FROM " + Constants.TABLE_MERCHANT_PLACE + 
					" 	WHERE " +
					"		merchant_id = '" + StringEscapeUtils.escapeSql(userid) + "' ";
			result = DBConn.query(sql);
			if (result.first()) {
				totalNum += result.getInt("total_num");
			}
		}
		
		if (type == Constants.PLACE_TYPE_COOP || type == Constants.PLACE_TYPE_ALL) {
			sql = "" +
					" 	SELECT " +
					"		A.id as place_id, A.place_name, A.address, A.gps_location, A.linkman, A.office_phone, " +
					"		A.mobile, A.business_item, 1 as type, B.status, B.id " +
					"	FROM " + Constants.TABLE_MERCHANT_PLACE + " A " +
					"		INNER JOIN " + Constants.TABLE_MERCHANT_PLACE_COOP + " B " +
					"		ON B.merchant_id = '" + StringEscapeUtils.escapeSql(userid) + "' " +
					"		AND A.id = B.place_id ";
			result = DBConn.query(sql);
			
			if (result.first()) {
				do {
					place = new MerchantPlace();
					place.setId(result.getString("id"));
					place.setPlaceId(result.getString("place_id"));
					place.setPlaceName(result.getString("place_name"));
					place.setAddress(result.getString("address"));
					place.setGpsLocation(result.getString("gps_location"));
					place.setLinkMan(result.getString("linkman"));
					place.setOfficePhone(result.getString("office_phone"));
					place.setMobile(result.getString("mobile"));
					place.setBusinessItem(result.getString("business_item"));
					place.setType(result.getString("type"));
					place.setStatus(result.getString("status"));
					
					ret.add(place);
				} while (result.next());
			}
			
			sql = "" +
					" 	SELECT " +
					"		count(1) as total_num " +
					"	FROM " + Constants.TABLE_MERCHANT_PLACE + " A " +
					"		INNER JOIN " + Constants.TABLE_MERCHANT_PLACE_COOP + " B " +
					"		ON B.merchant_id = '" + StringEscapeUtils.escapeSql(userid) + "' " +
					"		AND A.id = B.place_id ";
			result = DBConn.query(sql);
			if (result.first()) {
				totalNum += result.getInt("total_num");
			}
		}
		
		dataMap.put("items", ret);
		dataMap.put("total_num", totalNum);
		
		return dataMap;
	}
	
	/**
	 * 取得经营场所详情
	 */
	public static MerchantPlace getPlaceDetail(String userid, int type, String placeId) throws Exception{
		MerchantPlace place = null;
		ResultSet result;
		String sql = "";
		
		if (type == Constants.PLACE_TYPE_NORMAL) {
			sql = "" +
					" 	SELECT " +
					"		id, place_name, address, road_nearby, gps_location, linkman, office_phone, mobile, business_item, 0 as type, status" +
					" 	FROM " + Constants.TABLE_MERCHANT_PLACE + 
					" 	WHERE " +
					"		merchant_id = '" + StringEscapeUtils.escapeSql(userid) + "' " +
					" 	AND id = '" + StringEscapeUtils.escapeSql(placeId) + "'";
			result = DBConn.query(sql);
			
			if (result.first()) {
				place = new MerchantPlace();
				place.setId(result.getString("id"));
				place.setPlaceName(result.getString("place_name"));
				place.setAddress(result.getString("address"));
				place.setNearby(result.getString("road_nearby"));
				place.setGpsLocation(result.getString("gps_location"));
				place.setLinkMan(result.getString("linkman"));
				place.setOfficePhone(result.getString("office_phone"));
				place.setMobile(result.getString("mobile"));
				place.setBusinessItem(result.getString("business_item"));
				place.setType(result.getString("type"));
				place.setStatus(result.getString("status"));
			}
		}
		
		if (type == Constants.PLACE_TYPE_COOP) {
			sql = "" +
					" 	SELECT " +
					"		A.id, A.place_name, A.address, A.gps_location, A.linkman, A.office_phone, A.mobile, A.business_item, 1 as type, B.status " +
					"	FROM " + Constants.TABLE_MERCHANT_PLACE + " A " +
					"		INNER JOIN " + Constants.TABLE_MERCHANT_PLACE_COOP + " B " +
					"		ON B.merchant_id = '" + StringEscapeUtils.escapeSql(userid) + "' " +
					"		AND A.id = B.place_id " +
					" 	WHERE B.id = '" + StringEscapeUtils.escapeSql(placeId) + "'";
			result = DBConn.query(sql);
			
			if (result.first()) {
				place = new MerchantPlace();
				place.setId(result.getString("id"));
				place.setPlaceName(result.getString("place_name"));
				place.setAddress(result.getString("address"));
				place.setGpsLocation(result.getString("gps_location"));
				place.setLinkMan(result.getString("linkman"));
				place.setOfficePhone(result.getString("office_phone"));
				place.setMobile(result.getString("mobile"));
				place.setBusinessItem(result.getString("business_item"));
				place.setType(result.getString("type"));
				place.setStatus(result.getString("status"));
			}
		}
		
		return place;
	}
	

	/**
	 * 取得经营场所合作请求列表
	 */
	public static List<MerchantPlace> getPlaceRequests(String merchantId) throws Exception{
		List<MerchantPlace> ret = null;
		MerchantPlace place = null;
		ResultSet result;
		String lastPlaceId = "";
		String lastPlacename = "";
		List<PlaceRequest> requests = null;
		PlaceRequest request = null;
		
		String sql = "" +
				" 	SELECT " +
				"		A.id, A.place_id, C.address, B.corporation_name, A.status" +
				" 	FROM " + Constants.TABLE_MERCHANT_PLACE_COOP + " A " + 
				"		LEFT JOIN " + Constants.TABLE_MERCHANT + " B " +
				"		ON A.merchant_id = B.user_id " +
				"		LEFT JOIN " + Constants.TABLE_MERCHANT_PLACE + " C " +
				"		ON A.place_id = C.id " +
				" 	WHERE " +
				"		C.merchant_id = '" + StringEscapeUtils.escapeSql(merchantId) + "' " +
				"	ORDER BY A.place_id ";
		result = DBConn.query(sql);
		
		if (result.first()) {
			ret = new ArrayList<MerchantPlace>();
			requests = new ArrayList<PlaceRequest>();
			do {						
				request = new PlaceRequest();
				request.setRequestId(result.getString("id"));
				request.setCorporationName(result.getString("corporation_name"));
				request.setRequestStatus(result.getString("status"));
				
				if (!result.isFirst() && !lastPlaceId.equals(result.getString("place_id"))) {
					place = new MerchantPlace();
					place.setPlaceName(lastPlacename);
					place.setRequests(requests);
					
					ret.add(place);
					requests = new ArrayList<PlaceRequest>();
				}
				
				lastPlaceId = result.getString("place_id");
				lastPlacename = result.getString("address");
				requests.add(request);
				
				if (result.isLast()) {
					place = new MerchantPlace();
					place.setPlaceName(result.getString("address"));
					place.setRequests(requests);
					
					ret.add(place);
				}
			} while (result.next());
		}
		
		return ret;
	}
	
	/**
	 * 经营场所合作请求授权
	 */
	public static boolean authorization(String merchantId, String requestId, String authorization) throws Exception{
		int result;
		
		String sql = "" +
				" 	UPDATE " + Constants.TABLE_MERCHANT_PLACE_COOP + " A " +
				"		LEFT JOIN " + Constants.TABLE_MERCHANT_PLACE + " B " +
				"		ON A.place_id = B.id " +
				"	SET A.status = " + StringEscapeUtils.escapeSql(authorization) +
				"	WHERE A.id = " + StringEscapeUtils.escapeSql(requestId) +
				"	AND B.merchant_id = '" + StringEscapeUtils.escapeSql(merchantId) + "' ";
		result = DBConn.update(sql);
		
		return result > 0;
	}
	
	/**
	 * 经营场所合作请求授权(批量)
	 */
	public static boolean authorizationBatch(String merchantId, String requestIds) throws Exception{
		String[] requests = requestIds.split(",");
		String[] request = null;
		int authorization;
		
		for (int i = 0; i < requests.length; i++) {
			request = requests[i].split(":");
			if (request.length < 2) {continue;}
			
			if (request[1].equals(Constants.COOP_PLACE_STATUS_AUTHORIZED + "")) {
				authorization = Constants.COOP_PLACE_STATUS_AUTHORIZED;
			} else {
				authorization = Constants.COOP_PLACE_STATUS_UNAUTHORIZED;
			}
			
			String sql = "" +
					" 	UPDATE " + Constants.TABLE_MERCHANT_PLACE_COOP + " A " +
					"		LEFT JOIN " + Constants.TABLE_MERCHANT_PLACE + " B " +
					"		ON A.place_id = B.id " +
					"	SET A.status = " + authorization +
					"	WHERE A.id = " + StringEscapeUtils.escapeSql(request[0]) + 
					"	AND B.merchant_id = '" + StringEscapeUtils.escapeSql(merchantId) + "' ";
			DBConn.update(sql);
		}
		
		return true;
	}
	
	/**
	 * 查询客户资料
	 */
	public static Map<String, Object> searchCustomer(String merchantId, String customerId, String name, String mobile, 
			String licenseNumber, String model, int page, int pageSize) throws Exception{
		Map<String, Object> dataMap = new HashMap<String, Object>();
		List<Customer> ret = null;
		Customer customer = null;
		ResultSet result;
		String whereStr1 = "";
		String whereStr2 = "";
		//String whereStr1_2 = "";
		String merchantSqlStr = "";
		String limitStr = "";
		
		if (!Utils.empty(name)) {
			whereStr1 = "	AND name like '%" + StringEscapeUtils.escapeSql(name) + "%'";
			whereStr2 = "	AND A.customer_name like '%" + StringEscapeUtils.escapeSql(name) + "%'";
		}
		
		if (!Utils.empty(mobile)) {
			whereStr1 = "	AND mobile like '%" + StringEscapeUtils.escapeSql(mobile) + "%'";
			whereStr2 = "	AND A.customer_mobile like '%" + StringEscapeUtils.escapeSql(mobile) + "%'";
		}
		
		if (!Utils.empty(licenseNumber)) {
			whereStr1 += " 	AND license_number like '%" + StringEscapeUtils.escapeSql(licenseNumber) + "%' ";
			whereStr2 += "	AND A.license_number like '%" + StringEscapeUtils.escapeSql(licenseNumber) + "%' ";
		}
		
		if (!Utils.empty(model)) {
			whereStr1 += " 	AND model like '%" + StringEscapeUtils.escapeSql(model) + "%' ";
			whereStr2 += " 	AND model like '%" + StringEscapeUtils.escapeSql(model) + "%' ";
		}
		
		if (!Utils.empty(customerId)) {
			//whereStr1_2 = whereStr1;
			whereStr1 += " 	AND A.customer_id = '" + StringEscapeUtils.escapeSql(customerId) + "' ";
			//whereStr1_2 += " 	AND B.customer_id = '" + customerId + "' ";
			whereStr2 += " 	AND B.customer_id = '" + StringEscapeUtils.escapeSql(customerId) + "' ";
		}
		
		if (page > 0 && pageSize > 0) {
			limitStr = "   LIMIT " + (page - 1) * pageSize + ","  + pageSize;
		}
		
		merchantSqlStr = "" +
				"	( SELECT '" + StringEscapeUtils.escapeSql(merchantId) + "'" +
				"	UNION " +
				"	SELECT B.merchant_id " +
				"	FROM " + Constants.TABLE_MERCHANT_PLACE_COOP + " A " +
				"		LEFT JOIN " + Constants.TABLE_MERCHANT_PLACE + " B " +
				"		ON A.place_id = B.id " +
				"	WHERE A.merchant_id = '" + StringEscapeUtils.escapeSql(merchantId) + "' " +
				"	AND A.status = " + Constants.COOP_PLACE_STATUS_AUTHORIZED + ")";
		
		String sql = "" +
				" SELECT * FROM (" +
				" 	SELECT " +
				"		B.name, A.license_number, A.model, A.displacement, A.buy_date, A.photo, B.mobile, A.customer_id " +
				"	FROM " + Constants.TABLE_CAR_INFO + " A " +
				"		INNER JOIN " + Constants.TABLE_CUSTOMER + " B " +
				"		ON A.customer_id = B.user_id " +
				"		INNER JOIN " + Constants.TABLE_CUSTOMER_AUTHORIZATION + " C " +
				"		ON B.user_id = C.customer_id " +
				"		AND C.merchant_id IN " + merchantSqlStr +
				whereStr1 +
				" UNION DISTINCT " +
				"	SELECT * FROM (" +
				" 		SELECT " +
				"			A.customer_name as name, A.license_number, B.model, B.displacement, B.buy_date, B.photo, A.customer_mobile as mobile, B.customer_id " +
				"		FROM " + Constants.TABLE_MAINTAIN_ORDER + " A " +
				"			LEFT JOIN " + Constants.TABLE_CAR_INFO + " B " +
				" 			ON A.license_number = B.license_number " +
				"		WHERE (B.customer_id is null " +
				"		OR TRIM(B.customer_id) = '' )" +
				"		AND A.merchant_id IN " + merchantSqlStr +
				whereStr2 +
				"		ORDER BY A.maintain_date DESC" +
				"	) G GROUP BY license_number " +
				" ) H " +
				limitStr;
		
		result = DBConn.query(sql);
		
		if (result.first()) {
			ret = new ArrayList<Customer>();
			do {
				customer = new Customer();
				customer.setName(result.getString("name"));
				customer.setCarLicense(result.getString("license_number"));
				customer.setCarModel(result.getString("model"));
				customer.setCarDisplacement(result.getString("displacement"));
				customer.setBuyDate(result.getString("buy_date"));
				customer.setCarPhoto(Configs.uploadPath.replaceAll("\\\\", "/") + result.getString("photo"));
				customer.setMobile(result.getString("mobile"));
				customer.setId(result.getString("customer_id"));
				
				ret.add(customer);
			} while (result.next());
		}
		
		dataMap.put("items", ret);
		
		sql = "" +
				" SELECT count(1) AS total_num FROM (" +
				" 	SELECT " +
				"		B.name, A.license_number, A.model, A.displacement, A.buy_date, A.photo, B.mobile, A.customer_id " +
				"	FROM " + Constants.TABLE_CAR_INFO + " A " +
				"		INNER JOIN " + Constants.TABLE_CUSTOMER + " B " +
				"		ON A.customer_id = B.user_id " +
				"		INNER JOIN " + Constants.TABLE_CUSTOMER_AUTHORIZATION + " C " +
				"		ON B.user_id = C.customer_id " +
				"		AND C.merchant_id IN " + merchantSqlStr +
				whereStr1 +
				" UNION DISTINCT " +
				"	SELECT * FROM (" +
				" 		SELECT " +
				"			A.customer_name as name, B.license_number, B.model, B.displacement, B.buy_date, B.photo, A.customer_mobile as mobile, B.customer_id " +
				"		FROM " + Constants.TABLE_MAINTAIN_ORDER + " A " +
				"			LEFT JOIN " + Constants.TABLE_CAR_INFO + " B " +
				" 			ON A.license_number = B.license_number " +
				"		WHERE (B.customer_id is null " +
				"		OR TRIM(B.customer_id) = '' )" +
				"		AND A.merchant_id IN " + merchantSqlStr +
				whereStr2 +
				"		ORDER BY A.maintain_date DESC" +
				"	) G GROUP BY license_number " +
				" ) H ";
		
		result = DBConn.query(sql);
		
		if (result.first()) {
			dataMap.put("total_num", result.getString("total_num"));
		}
		
		
		return dataMap;
	}
	
	/**
	 * 取得请求列表
	 */
	public static Map<String, Object> getRequests(String requestType, String status, int page, int pageSize, String merchantId) throws Exception{
		Map<String, Object> dataMap = new HashMap<String, Object>();
		List<Request> ret = null;
		Request request = null;
		ResultSet result;
		String whereStr = "";
		
		if (!Utils.empty(requestType)) {
			whereStr = "	WHERE A.type = " + StringEscapeUtils.escapeSql(requestType);
		}
		
		if (!Utils.empty(status)) {
			whereStr += Utils.empty(whereStr) ? " WHERE " : " AND ";
			whereStr += "	A.status = " + StringEscapeUtils.escapeSql(status);
			
			if (status.equals(Constants.REQUEST_STATUS_ACCEPTED + "")) {
				whereStr += Utils.empty(whereStr) ? " WHERE " : " AND ";
				whereStr += "	A.merchant_id = '" + StringEscapeUtils.escapeSql(merchantId) + "' ";
			}
		} else {
			whereStr += Utils.empty(whereStr) ? " WHERE " : " AND ";
			whereStr += "	A.status != " + Constants.REQUEST_STATUS_CANCELED;
			whereStr += "	AND (A.merchant_id = '' ";
			whereStr += "		OR A.merchant_id = '" + StringEscapeUtils.escapeSql(merchantId) + "') ";
		}
		
		whereStr += Utils.empty(whereStr) ? " WHERE " : " AND ";
		whereStr += "	(A.type = " + Constants.REQUEST_TYPE_MAINTAIN;
		whereStr += "		OR (A.type = " + Constants.REQUEST_TYPE_HELP;
		whereStr += "		AND A.request_limit > " + System.currentTimeMillis() + "))";
		
		String sql = "" +
				" 	SELECT " +
				"		A.id, A.type, A.request_content, A.request_date, A.request_limit, A.status, A.current_mileage, B.license_number, B.model, " +
				"		B.displacement, A.area, A.address, A.gps_location, C.name, C.mobile " +
				" 	FROM " + Constants.TABLE_REQUEST + " A " + 
				"		LEFT JOIN " + Constants.TABLE_CAR_INFO + " B " +
				"		ON A.car_license = B.license_number " +
				"		LEFT JOIN " + Constants.TABLE_CUSTOMER + " C " +
				"		ON A.customer_id = C.user_id " +
				whereStr +
				" 	ORDER BY A.Id DESC " +
				"   LIMIT " + (page - 1) * pageSize + ","  + pageSize;
		
		result = DBConn.query(sql);
		
		if (result.first()) {
			ret = new ArrayList<Request>();
			do {				
				request = new Request();
				
				if (result.getInt("type") == Constants.REQUEST_TYPE_HELP) {
					request.setRemainTime(result.getLong("request_limit") - System.currentTimeMillis() + "");
				}

				request.setId(result.getString("id"));
				request.setType(result.getString("type"));
				request.setContent(result.getString("request_content"));
				request.setDate(result.getDate("request_date").toString() + " " + result.getTime("request_date").toString());
				request.setRequestStatus(result.getString("status"));
				request.setLicenseNumber(result.getString("license_number"));
				request.setCurrentMileage(result.getString("current_mileage"));
				request.setCarModel(result.getString("model"));
				request.setDisplacement(result.getString("displacement"));
				request.setArea(result.getString("area"));
				request.setAddress(result.getString("address"));
				request.setGpsLocation(result.getString("gps_location"));
				request.setCustomerName(result.getString("name"));
				request.setCustomerMobile(result.getString("mobile"));
				
				ret.add(request);
			} while (result.next());
		}
		
		dataMap.put("items", ret);
		
		sql = "" +
				" 	SELECT " +
				"		count(1) as total_num " +
				" 	FROM " + Constants.TABLE_REQUEST + " A " + 
				"		LEFT JOIN " + Constants.TABLE_CAR_INFO + " B " +
				"		ON A.car_license = B.license_number " +
				"		LEFT JOIN " + Constants.TABLE_CUSTOMER + " C " +
				"		ON A.customer_id = C.user_id " +
				whereStr;
		result = DBConn.query(sql);
		
		if (result.first()) {
			dataMap.put("total_num", result.getString("total_num"));
		}
		
		return dataMap;
	}
	
	/**
	 * 商家接受请求
	 */
	public static boolean acceptRequest(String merchantId, String requestId, String requestType) throws Exception{
		String sql = "";
		String orderId = "";
		
		if (requestType.equals(Constants.REQUEST_TYPE_MAINTAIN + "")) {
			orderId = Utils.getStrDate(System.currentTimeMillis()) + System.currentTimeMillis();
			MerchantLib.addMaintainOrder(orderId, merchantId, "", "", "", "0", Utils.getStrDate2(System.currentTimeMillis()), "0", "0", "", null);
		}
		
		sql = "" +
				" UPDATE " +
				"	" + Constants.TABLE_REQUEST + 
				" SET " +
				"	order_id = '" + StringEscapeUtils.escapeSql(orderId) + "', " + 
				"	merchant_id = '" + StringEscapeUtils.escapeSql(merchantId) + "', " + 
				"	status = '" + Constants.REQUEST_STATUS_ACCEPTED +  "'" +
				" WHERE id = '" + StringEscapeUtils.escapeSql(requestId) + "' " +
				" AND type = '" + StringEscapeUtils.escapeSql(requestType) + "'" +
				" AND status = '" + Constants.REQUEST_STATUS_WAITING + "'";
		
		DBConn.update(sql);
		
		return true;
	}
	
	/**
	 * 提交审核
	 */
	public static boolean RequestVerify(String merchantId, String[] images) throws Exception{
		String sql = "";
		
		if (images != null && images.length > 0) {
			//删除现有图片
			sql = "" +
					" DELETE FROM " + Constants.TABLE_MERCHANT_PHOTO +
					" WHERE merchant_id = '" + StringEscapeUtils.escapeSql(merchantId) + "'";
			
			DBConn.update(sql);
			
			//插入新图片
			for (int i = 0; i < images.length; i++) {
				sql = "" +
						" INSERT INTO " + Constants.TABLE_MERCHANT_PHOTO +
						" 	(merchant_id, image_name)" +
						" VALUES (" +
						" 	'" + StringEscapeUtils.escapeSql(merchantId) + "', " +
						"	'" + StringEscapeUtils.escapeSql(images[i]) + "')";
				
				DBConn.update(sql);
			}
		}
		
		sql = "" +
				"	UPDATE " + Constants.TABLE_MERCHANT_PLACE +
				"	SET status = " + Constants.PLACE_STATUS_WAITING_VERIFY +
				"	WHERE merchant_id = '" + StringEscapeUtils.escapeSql(merchantId) + "' " +
				"	AND status = " + Constants.PLACE_STATUS_UNVERIFIED;

		DBConn.update(sql);

		sql = "" +
				"	UPDATE " + Constants.TABLE_MERCHANT_PLACE_COOP +
				"	SET status = " + Constants.COOP_PLACE_STATUS_UNAUTHORIZED +
				"	WHERE merchant_id = '" + StringEscapeUtils.escapeSql(merchantId) + "' " +
				"	AND status = " + Constants.COOP_PLACE_STATUS_UNREQUEST;

		DBConn.update(sql);
		
		return true;
	}
	
	/**
	 * 添加养护记录
	 */
	public static int addMaintainOrder(String orderId, String merchantId, String licenseNumber, String username, String mobile, 
			String mileage, String maintainDate, String maintainPlace, String maintainFee, String comment, 
			JSONArray maintainItems) throws Exception{

		String sql = "" +
				" INSERT INTO" +
				"	" + Constants.TABLE_MAINTAIN_ORDER + 
				"	(order_id, license_number, merchant_id, place_id, customer_name, customer_mobile, mileage, maintain_date, maintain_fee, comment, status) " +
				" VALUES " +
				"	('" + StringEscapeUtils.escapeSql(orderId) + "', " +
				"	'" + StringEscapeUtils.escapeSql(licenseNumber) + "', " + 
				"	'" + StringEscapeUtils.escapeSql(merchantId) + "', " + 
				"	'" + StringEscapeUtils.escapeSql(maintainPlace) + "', " + 
				"	'" + StringEscapeUtils.escapeSql(username) + "', " + 
				"	'" + StringEscapeUtils.escapeSql(mobile) + "', " + 
				"	'" + StringEscapeUtils.escapeSql(mileage) + "', " + 
				"	'" + StringEscapeUtils.escapeSql(maintainDate) + "', " + 
				"	'" + StringEscapeUtils.escapeSql(maintainFee) + "', " + 
				"	'" + StringEscapeUtils.escapeSql(comment) + "', " + 
				"	'" + Constants.MAINTAIN_STATUS_WAIT +  "')";
		int affectedRows = DBConn.update(sql);
		
		if (affectedRows > 0) {
			if (maintainItems != null) {
				for (int i = 0; i < maintainItems.size(); i++) {
					JSONObject item = maintainItems.getJSONObject(i);
					sql = "" +
							" INSERT INTO" +
							"	" + Constants.TABLE_MAINTAIN_ITEM + 
							"	(order_id, item_id, item_brand, item_type, item_period, item_model) " +
							" VALUES " +
							"	('" + StringEscapeUtils.escapeSql(orderId) + "', " +
							"	'" + StringEscapeUtils.escapeSql(item.getString("item_id")) + "', " + 
							"	'" + StringEscapeUtils.escapeSql(item.getString("item_brand")) + "', " + 
							"	'" + StringEscapeUtils.escapeSql(item.getString("item_type")) + "', " + 
							"	'" + StringEscapeUtils.escapeSql(item.getString("item_period")) + "', " + 
							"	'" + StringEscapeUtils.escapeSql(item.getString("item_model")) +  "')";
					
					DBConn.update(sql);
				}
			}
			
			sql = "" +
					" INSERT INTO" +
					"	" + Constants.TABLE_MESSAGE_RECEIVE + 
					"	(sender_id, receiver_id, type, msg_title, msg_content, status, receive_date) " +
					" VALUES " +
					"	('" + StringEscapeUtils.escapeSql(merchantId) + "', " +
					"	'" + "test" + "', " + 
					"	'" + Constants.MSG_TYPE_MERCHANT_TO_CUSTOMER + "', " + 
					"	'" + StringEscapeUtils.escapeSql(maintainDate + "养护提醒") + "', " + 
					"	'" + StringEscapeUtils.escapeSql("养护提醒养护提醒养护提醒养护提醒") + "', " + 
					"	'" + Constants.MSG_STATUS_UNREAD + "', " + 
					"	'" + Utils.getStrTime(System.currentTimeMillis()) +  "')";
			DBConn.update(sql);
			
			return Constants.CODE_SUCCESS;
		} else {
			return Constants.CODE_FAIL;
		}
	}
	
	/**
	 * 修改养护记录
	 */
	public static int modifyMaintainOrder(String merchantId, String orderId,
			String mileage, String maintainDate, String maintainPlace, String maintainFee, String comment, 
			JSONArray maintainItems) throws Exception{
		ResultSet result;
		String maintainMerchant = "";
		
		//取得指定养护场所的商家ID
		String sql = "" +
				" SELECT B.merchant_id " +
				" FROM " + Constants.TABLE_MAINTAIN_ORDER + " A " +
				"	LEFT JOIN " + Constants.TABLE_MERCHANT_PLACE + " B " +
				"	ON A.order_id = '" + StringEscapeUtils.escapeSql(orderId) + "'" +
				"	AND A.place_id = B.id " +
				" WHERE A.order_id = '" + StringEscapeUtils.escapeSql(orderId) + "'";
		result = DBConn.query(sql);
		if (result.first()) {
			maintainMerchant = result.getString("merchant_id");
		}

		sql = "" +
				" UPDATE " + Constants.TABLE_MAINTAIN_ORDER +
				" SET " +
				"	place_id = '" + StringEscapeUtils.escapeSql(maintainPlace) + "', " +
				"	mileage = '" + StringEscapeUtils.escapeSql(mileage) + "', " +
				"	maintain_date = '" + StringEscapeUtils.escapeSql(maintainDate) + "', " +
				"	maintain_fee = '" + StringEscapeUtils.escapeSql(maintainFee) + "', " +
				"	comment = '" + StringEscapeUtils.escapeSql(comment) + "' " +
				" WHERE order_id = '" + StringEscapeUtils.escapeSql(orderId) + "' " +
				" AND (merchant_id = '" + StringEscapeUtils.escapeSql(merchantId) + "' " +
				"		OR '" + StringEscapeUtils.escapeSql(merchantId) + "' = '" + StringEscapeUtils.escapeSql(maintainMerchant) + "')";
		int affectedRows = DBConn.update(sql);
		
		if (affectedRows >0) {
			sql = "" +
					" DELETE FROM " +
					"	" + Constants.TABLE_MAINTAIN_ITEM + 
					" WHERE order_id = '" + StringEscapeUtils.escapeSql(orderId) + "' ";
			
			DBConn.update(sql);
			
			if (maintainItems != null) {
				for (int i = 0; i < maintainItems.size(); i++) {
					JSONObject item = maintainItems.getJSONObject(i);
					sql = "" +
							" INSERT INTO" +
							"	" + Constants.TABLE_MAINTAIN_ITEM + 
							"	(order_id, item_id, item_brand, item_type, item_period, item_model) " +
							" VALUES " +
							"	('" + StringEscapeUtils.escapeSql(orderId) + "', " +
							"	'" + StringEscapeUtils.escapeSql(item.getString("item_id")) + "', " + 
							"	'" + StringEscapeUtils.escapeSql(item.getString("item_brand")) + "', " + 
							"	'" + StringEscapeUtils.escapeSql(item.getString("item_type")) + "', " + 
							"	'" + StringEscapeUtils.escapeSql(item.getString("item_period")) + "', " + 
							"	'" + StringEscapeUtils.escapeSql(item.getString("item_model")) +  "')";
					
					DBConn.update(sql);
				}
			}
			
			sql = "" +
					" INSERT INTO" +
					"	" + Constants.TABLE_MESSAGE_RECEIVE + 
					"	(sender_id, receiver_id, type, msg_title, msg_content, status, receive_date) " +
					" VALUES " +
					"	('" + StringEscapeUtils.escapeSql(merchantId) + "', " +
					"	'" + "test" + "', " + 
					"	'" + Constants.MSG_TYPE_MERCHANT_TO_CUSTOMER + "', " + 
					"	'" + StringEscapeUtils.escapeSql(maintainDate + "养护更新提醒") + "', " + 
					"	'" + StringEscapeUtils.escapeSql("养护提醒养护提醒养护提醒养护提醒") + "', " + 
					"	'" + Constants.MSG_STATUS_UNREAD + "', " + 
					"	'" + Utils.getStrTime(System.currentTimeMillis()) +  "')";
			DBConn.update(sql);
			
			return Constants.CODE_SUCCESS;
		} else {
			return Constants.CODE_FAIL;
		}
	}
	
	/**
	 * 添加养护项目及快捷方式
	 */
	public static int addMaintainItemAndShortcut(String merchantId, String orderId, JSONObject maintainItem) throws Exception{
		ResultSet result;
		String maintainMerchant = "";
		
		//取得指定养护场所的商家ID
		String sql = "" +
				" SELECT B.merchant_id " +
				" FROM " + Constants.TABLE_MAINTAIN_ORDER + " A " +
				"	LEFT JOIN " + Constants.TABLE_MERCHANT_PLACE + " B " +
				"	ON A.order_id = '" + StringEscapeUtils.escapeSql(orderId) + "'" +
				"	AND A.place_id = B.id " +
				" WHERE A.order_id = '" + StringEscapeUtils.escapeSql(orderId) + "'";
		result = DBConn.query(sql);
		if (result.first()) {
			maintainMerchant = result.getString("merchant_id");
		}


			
		if (maintainItem != null) {
			sql = "" +
					" INSERT INTO" +
					"	" + Constants.TABLE_MAINTAIN_ITEM + 
					"	(order_id, item_id, item_brand, item_type, item_period, item_model) " +
					" VALUES " +
					"	('" + StringEscapeUtils.escapeSql(orderId) + "', " +
					"	'" + StringEscapeUtils.escapeSql(maintainItem.getString("item_brand")) + "', " + 
					"	'" + StringEscapeUtils.escapeSql(maintainItem.getString("item_type")) + "', " + 
					"	'" + StringEscapeUtils.escapeSql(maintainItem.getString("item_period")) + "', " + 
					"	'" + StringEscapeUtils.escapeSql(maintainItem.getString("item_model")) +  "')";
			
			DBConn.update(sql);
		}
		
		sql = "" +
				" INSERT INTO" +
				"	" + Constants.TABLE_MESSAGE_RECEIVE + 
				"	(sender_id, receiver_id, type, msg_title, msg_content, status, receive_date) " +
				" VALUES " +
				"	('" + StringEscapeUtils.escapeSql(merchantId) + "', " +
				"	'" + "test" + "', " + 
				"	'" + Constants.MSG_TYPE_MERCHANT_TO_CUSTOMER + "', " + 
				"	'" + StringEscapeUtils.escapeSql("养护更新提醒") + "', " + 
				"	'" + StringEscapeUtils.escapeSql("养护提醒养护提醒养护提醒养护提醒") + "', " + 
				"	'" + Constants.MSG_STATUS_UNREAD + "', " + 
				"	'" + Utils.getStrTime(System.currentTimeMillis()) +  "')";
		DBConn.update(sql);
		
		return Constants.CODE_SUCCESS;
	}
	
	/**
	 * 取得维护历史列表
	 */
	public static Map<String, Object> getMaintainHistory(String merchantId, String carLicense, String nameOrMobile, String status, String startDate,
			String endDate, int page, int pageSize) throws Exception{
		Map<String, Object> dataMap = new HashMap<String, Object>();
		List<MaintainHistory> ret = null;
		MaintainHistory maintainOrder = null;
		ResultSet result;
		String whereStr = "";
		String dateAndStatusStr = "";
		String limitStr = "";

		if (!Utils.empty(carLicense)) {
			whereStr += Utils.empty(whereStr) ? "	WHERE " : "	AND ";
			whereStr += "	A.license_number like '%" + StringEscapeUtils.escapeSql(carLicense) + "%' ";
		}
		if (!Utils.empty(nameOrMobile)) {
			whereStr += Utils.empty(whereStr) ? "	WHERE " : "	AND ";
			whereStr += "	(customer_name like '%" + StringEscapeUtils.escapeSql(nameOrMobile) + "%'";
			whereStr += "	OR customer_mobile like '%" + StringEscapeUtils.escapeSql(nameOrMobile) + "%'";
			whereStr += "	OR C.name like '%" + StringEscapeUtils.escapeSql(nameOrMobile) + "%'";
			whereStr += "	OR C.mobile like '%" + StringEscapeUtils.escapeSql(nameOrMobile) + "%') ";
		}
		if (!Utils.empty(startDate)) {
			dateAndStatusStr += Utils.empty(whereStr + dateAndStatusStr) ? "	WHERE " : "	AND ";
			dateAndStatusStr += "	maintain_date >= '" + StringEscapeUtils.escapeSql(startDate) + "' "; 
		}
		if (!Utils.empty(endDate)) {
			dateAndStatusStr += Utils.empty(whereStr + dateAndStatusStr) ? "	WHERE " : "	AND ";
			dateAndStatusStr += "	maintain_date <= '" + StringEscapeUtils.escapeSql(endDate) + "' "; 
		}
		if (!Utils.empty(status)) {
			dateAndStatusStr += Utils.empty(whereStr + dateAndStatusStr) ? "	WHERE " : "	AND ";
			if (("" + Constants.MAINTAIN_STATUS_MAINTAIN_COMPLETE).equals(status)) {
				dateAndStatusStr += "	(A.status = " + Constants.MAINTAIN_STATUS_WAIT_COMMENT;
				dateAndStatusStr += "	OR A.status = " + Constants.MAINTAIN_STATUS_WAIT_CONFIRM;
				dateAndStatusStr += "	OR A.status = " + Constants.MAINTAIN_STATUS_COMMENT_COMPLETE + ")";
			} else {
				dateAndStatusStr += "	A.status = " + StringEscapeUtils.escapeSql(status);
			}
		}
		
		if (page > 0 && pageSize > 0) {
			limitStr = "   LIMIT " + (page - 1) * pageSize + ","  + pageSize;
		}
		
		String sql = "" +
				" 	SELECT " +
				"		A.order_id, A.license_number, A.merchant_id, " +
				"		C.name, C.mobile, A.customer_name, A.customer_mobile, " +
				"		A.mileage, A.maintain_date, D.address, A.maintain_fee, A.comment, A.status, B.model " +
				" 	FROM " + Constants.TABLE_MAINTAIN_ORDER + " A " +
				"		LEFT JOIN " + Constants.TABLE_CAR_INFO + " B " +
				"		ON A.license_number = B.license_number " +
				"		LEFT JOIN (" + Constants.TABLE_CUSTOMER + " C " +
				"			INNER JOIN " + Constants.TABLE_CUSTOMER_AUTHORIZATION + " E" +
				"			ON C.user_id = E.customer_id" +
				"			AND E.merchant_id = '" + StringEscapeUtils.escapeSql(merchantId) + "') " +
				"		ON B.customer_id = C.user_id " +
				"		LEFT JOIN " + Constants.TABLE_MERCHANT_PLACE + " D " +
				"		ON A.place_id = D.id " +
				whereStr +
				dateAndStatusStr +
				" 	ORDER BY A.maintain_date DESC " +
				limitStr;
		
		result = DBConn.query(sql);
		
		if (result.first()) {
			ret = new ArrayList<MaintainHistory>();
			do {
				maintainOrder = new MaintainHistory();
				maintainOrder.setId(result.getString("order_id"));
				maintainOrder.setLicenseNumber(result.getString("license_number"));
				maintainOrder.setMileage(result.getString("mileage"));
				maintainOrder.setMaintainDate(result.getString("maintain_date"));
				maintainOrder.setStatus(result.getString("status"));
				
				ret.add(maintainOrder);
			} while (result.next());
			
			dataMap.put("items", ret);
		}
		
		sql = "" +
				" 	SELECT " +
				"		count(1) as total_num" +
				" 	FROM " + Constants.TABLE_MAINTAIN_ORDER + " A " +
				"		LEFT JOIN " + Constants.TABLE_CAR_INFO + " B " +
				"		ON A.license_number = B.license_number " +
				"		LEFT JOIN " + Constants.TABLE_CUSTOMER + " C " +
				"		ON B.customer_id = C.user_id " +
				"		LEFT JOIN " + Constants.TABLE_MERCHANT_PLACE + " D " +
				"		ON A.place_id = D.id " +
				whereStr +
				dateAndStatusStr;
		result = DBConn.query(sql);
		if (result.first()) {
			dataMap.put("total_num", result.getString("total_num"));
		}
		
		sql = "" +
				" 	SELECT " +
				"		count(1) as waiting_num" +
				" 	FROM " + Constants.TABLE_MAINTAIN_ORDER + " A " +
				"		LEFT JOIN " + Constants.TABLE_CAR_INFO + " B " +
				"		ON A.license_number = B.license_number " +
				"		LEFT JOIN " + Constants.TABLE_CUSTOMER + " C " +
				"		ON B.customer_id = C.user_id " +
				"		LEFT JOIN " + Constants.TABLE_MERCHANT_PLACE + " D " +
				"		ON A.place_id = D.id " +
				whereStr + (Utils.empty(whereStr) ? "	WHERE " : "	AND ") +
				"	A.status = " + Constants.MAINTAIN_STATUS_WAIT;
		result = DBConn.query(sql);
		if (result.first()) {
			dataMap.put("waiting_num", result.getString("waiting_num"));
		}
		
		return dataMap;
	}
	
	/**
	 * 取得维护历史详情
	 */
	public static MaintainHistory getMaintainDetail(String merchantId, String orderId) throws Exception{
		MaintainHistory ret = null;
		ResultSet result;
		ResultSet result2;
		Map<String, String> authorizedMerchants = new HashMap<String, String>();
		List<MaintainItem> maintainItems = null;
		MaintainItem maintainItem = null;
		
		String sql = "" +
				" 	SELECT " +
				"		A.order_id, A.license_number, A.merchant_id, " +
				"		C.name, C.mobile, A.customer_name, A.customer_mobile, " +
				"		A.mileage, A.maintain_date, A.place_id, D.address, A.maintain_fee, A.comment, A.status, B.model " +
				" 	FROM " + Constants.TABLE_MAINTAIN_ORDER + " A " +
				"		LEFT JOIN " + Constants.TABLE_CAR_INFO + " B " +
				"		ON A.license_number = B.license_number " +
				"		LEFT JOIN (" + Constants.TABLE_CUSTOMER + " C " +
				"			INNER JOIN " + Constants.TABLE_CUSTOMER_AUTHORIZATION + " E" +
				"			ON C.user_id = E.customer_id" +
				"			AND E.merchant_id = '" + StringEscapeUtils.escapeSql(merchantId) + "') " +
				"		ON B.customer_id = C.user_id " +
				"		LEFT JOIN " + Constants.TABLE_MERCHANT_PLACE + " D " +
				"		ON A.place_id = D.id " +
				"	WHERE A.order_id = '" + StringEscapeUtils.escapeSql(orderId) + "'" +
				" 	ORDER BY A.order_id DESC ";
		
		result = DBConn.query(sql);
		
		if (result.first()) {
			//取得有权限商家列表
			sql = "" +
					" 	SELECT B.merchant_id " +
					" 	FROM " + Constants.TABLE_MAINTAIN_ORDER + " A " +
					"		LEFT JOIN " + Constants.TABLE_MERCHANT_PLACE + " B " +
					"		ON A.order_id = '" + StringEscapeUtils.escapeSql(orderId) + "'" +
					"		AND A.place_id = B.id " +
					" 	WHERE A.order_id = '" + StringEscapeUtils.escapeSql(orderId) + "'" +
					" 	AND B.merchant_id is not null " +
					" UNION " +
					" 	SELECT merchant_id " +
					" 	FROM " + Constants.TABLE_MAINTAIN_ORDER +
					" 	WHERE order_id = '" + StringEscapeUtils.escapeSql(orderId) + "'" +
					" UNION " +
					"	SELECT B.merchant_id " +
					"	FROM " + Constants.TABLE_MERCHANT_PLACE_COOP + " A " +
					"		LEFT JOIN " + Constants.TABLE_MERCHANT_PLACE + " B " +
					"		ON A.place_id = B.id " +
					"	WHERE A.place_id = '" + StringEscapeUtils.escapeSql(result.getString("place_id")) + "'" +
					"	AND A.status = " + Constants.COOP_PLACE_STATUS_AUTHORIZED;

			result2 = DBConn.query(sql);
			
			if (result2.first()) {
				do {
					authorizedMerchants.put(result2.getString("merchant_id"), result2.getString("merchant_id"));
				} while (result2.next());
			}
			
			ret = new MaintainHistory();
			
			ret.setId(result.getString("order_id"));
			ret.setLicenseNumber(result.getString("license_number"));
			ret.setMileage(result.getString("mileage"));
			ret.setMaintainDate(result.getString("maintain_date"));
			
			//if the order is the mechant's own data, display the address
			if (authorizedMerchants.containsKey(merchantId)) {
				ret.setPlaceId(result.getString("place_id"));
				ret.setAddress(result.getString("address"));
				ret.setMaintainFee(result.getString("maintain_fee"));
			}
			ret.setComment(result.getString("comment"));
			ret.setStatus(result.getString("status"));
			
			//取得维护项目列表
			sql = "" +
					" SELECT " +
					"	A.id, A.item_id, A.item_brand, A.item_type, A.item_period, A.item_model, A.buy_only, " +
					"	B.brand_id, B.type_id, B.period_id, B.model, C.brand_name, D.period_name, E.type_name " +
					" FROM " + Constants.TABLE_MAINTAIN_ITEM + " A " +
					" 	LEFT JOIN " + Constants.TABLE_ITEM + " B " +
					"	ON A.item_id = B.id " +
					"	LEFT JOIN " + Constants.TABLE_ITEM_BRAND + " C " +
					"	ON B.brand_id = C.id " +
					"	LEFT JOIN " + Constants.TABLE_ITEM_PERIOD + " D " +
					"	ON B.period_id = D.id " +
					"	LEFT JOIN " + Constants.TABLE_ITEM_TYPE + " E " +
					"	ON B.type_id = E.id " +
					" WHERE A.order_id = '" + StringEscapeUtils.escapeSql(orderId) + "'";
			result = DBConn.query(sql);
			if (result.first()) {
				maintainItems = new ArrayList<MaintainItem>();
				do {
					maintainItem = new MaintainItem();
					
					if (!Utils.empty(result.getString("item_id"))) {
						maintainItem.setId(result.getString("id"));
						maintainItem.setItemId(result.getString("item_id"));
						maintainItem.setItemBrand(result.getString("brand_id"));
						maintainItem.setItemType(result.getString("type_id"));
						maintainItem.setItemPeriod(result.getString("period_id"));
						maintainItem.setItemModel(result.getString("model"));
						
						maintainItem.setItemBrandName(result.getString("brand_name"));
						maintainItem.setItemTypeName(result.getString("type_name"));
						maintainItem.setItemPeriodName(result.getString("period_name"));

						maintainItem.setBuyOnly(result.getString("buy_only"));
					} else {
						maintainItem.setItemBrandName(result.getString("item_brand"));
						maintainItem.setItemTypeName(result.getString("item_type"));
						maintainItem.setItemPeriodName(result.getString("item_period"));
						maintainItem.setItemModel(result.getString("item_model"));
					}
					
					maintainItems.add(maintainItem);
				} while(result.next());
				
				ret.setMaintainItems(maintainItems);
			}
		}
		
		return ret;
	}
	
	/**
	 * 取得保养车辆信息
	 */
	public static Map<String, Object> getMaintainCars(String merchantId, String status, String startDate,
			String endDate, int page, int pageSize) throws Exception{
		Map<String, Object> dataMap = new HashMap<String, Object>();
		List<Customer> ret = null;
		Customer customer = null;
		ResultSet result;
		String whereStr = "";
		String limitStr = "";
		
		if (!Utils.empty(startDate)) {
			whereStr += "	AND maintain_date >= '" + StringEscapeUtils.escapeSql(startDate) + "' "; 
		}
		if (!Utils.empty(endDate)) {
			whereStr += "	AND maintain_date <= '" + StringEscapeUtils.escapeSql(endDate) + "' "; 
		}
		if (!Utils.empty(status)) {
			if ("9".equals(status)) {
				whereStr += "	AND (A.status = " + Constants.MAINTAIN_STATUS_WAIT_COMMENT;
				whereStr += "	OR A.status = " + Constants.MAINTAIN_STATUS_WAIT_CONFIRM;
				whereStr += "	OR A.status = " + Constants.MAINTAIN_STATUS_COMMENT_COMPLETE + ")";
			} else {
				whereStr += "	AND A.status = " + StringEscapeUtils.escapeSql(status);
			}
		}
		
		if (page > 0 && pageSize > 0) {
			limitStr = "   LIMIT " + (page - 1) * pageSize + ","  + pageSize;
		}
		
		String sql = "" +
				" 	SELECT " +
				"		A.order_id, A.license_number, A.merchant_id, A.place_id, " +
				"		C.user_id AS customer_id, C.name, C.mobile, A.customer_name, A.customer_mobile, " +
				"		A.mileage, A.maintain_date, D.address, A.maintain_fee, A.comment, A.status, B.model " +
				" 	FROM " + Constants.TABLE_MAINTAIN_ORDER + " A " +
				"		LEFT JOIN " + Constants.TABLE_CAR_INFO + " B " +
				"		ON A.license_number = B.license_number " +
				"		LEFT JOIN (" + Constants.TABLE_CUSTOMER + " C " +
				"			INNER JOIN " + Constants.TABLE_CUSTOMER_AUTHORIZATION + " E" +
				"			ON C.user_id = E.customer_id" +
				"			AND E.merchant_id = '" + StringEscapeUtils.escapeSql(merchantId) + "') " +
				"		ON B.customer_id = C.user_id " +
				"		LEFT JOIN " + Constants.TABLE_MERCHANT_PLACE + " D " +
				"		ON A.place_id = D.id " +
				"	WHERE '" + StringEscapeUtils.escapeSql(merchantId) + "' IN (" +
				" 	SELECT I.merchant_id " +
				" 	FROM " + Constants.TABLE_MAINTAIN_ORDER + " H " +
				"		LEFT JOIN " + Constants.TABLE_MERCHANT_PLACE + " I " +
				"		ON H.place_id = I.id " +
				" 	WHERE H.order_id = A.order_id " +
				" 	AND I.merchant_id is not null " +
				" UNION " +
				" 	SELECT merchant_id " +
				" 	FROM " + Constants.TABLE_MAINTAIN_ORDER +
				" 	WHERE order_id = A.order_id " +
				" UNION " +
				"	SELECT K.merchant_id " +
				"	FROM " + Constants.TABLE_MERCHANT_PLACE_COOP + " J " +
				"		LEFT JOIN " + Constants.TABLE_MERCHANT_PLACE + " K " +
				"		ON J.place_id = K.id " +
				"	WHERE J.place_id = A.place_id " +
				"	AND J.status = " + Constants.COOP_PLACE_STATUS_AUTHORIZED + ") " +
				whereStr +
				" 	ORDER BY A.maintain_date DESC " +
				limitStr;
		
		result = DBConn.query(sql);
		
		if (result.first()) {
			ret = new ArrayList<Customer>();
			do {				
				customer = new Customer();
				customer.setOrderId(result.getString("order_id"));
				if (!Utils.empty(result.getString("name"))) {
					customer.setId(result.getString("customer_id"));
					customer.setName(result.getString("name"));
					customer.setMobile(result.getString("mobile"));
				} else {
					customer.setName(result.getString("customer_name"));
					customer.setMobile(result.getString("customer_mobile"));					
				}
				customer.setCarLicense(result.getString("license_number"));
				customer.setCarModel(result.getString("model"));
				customer.setMaintainDate(result.getString("maintain_date"));
				customer.setStatus(result.getString("status"));
				
				ret.add(customer);
			} while (result.next());
			
			dataMap.put("items", ret);
		}
		
		sql = "" +
				" 	SELECT " +
				"		count(1) as total_num" +
				" 	FROM " + Constants.TABLE_MAINTAIN_ORDER + " A " +
				"		LEFT JOIN " + Constants.TABLE_CAR_INFO + " B " +
				"		ON A.license_number = B.license_number " +
				"		LEFT JOIN (" + Constants.TABLE_CUSTOMER + " C " +
				"			INNER JOIN " + Constants.TABLE_CUSTOMER_AUTHORIZATION + " E" +
				"			ON C.user_id = E.customer_id" +
				"			AND E.merchant_id = '" + StringEscapeUtils.escapeSql(merchantId) + "') " +
				"		ON B.customer_id = C.user_id " +
				"		LEFT JOIN " + Constants.TABLE_MERCHANT_PLACE + " D " +
				"		ON A.place_id = D.id " +
				"	WHERE '" + StringEscapeUtils.escapeSql(merchantId) + "' IN (" +
				" 	SELECT I.merchant_id " +
				" 	FROM " + Constants.TABLE_MAINTAIN_ORDER + " H " +
				"		LEFT JOIN " + Constants.TABLE_MERCHANT_PLACE + " I " +
				"		ON H.place_id = I.id " +
				" 	WHERE H.order_id = A.order_id " +
				" 	AND I.merchant_id is not null " +
				" UNION " +
				" 	SELECT merchant_id " +
				" 	FROM " + Constants.TABLE_MAINTAIN_ORDER +
				" 	WHERE order_id = A.order_id " +
				" UNION " +
				"	SELECT K.merchant_id " +
				"	FROM " + Constants.TABLE_MERCHANT_PLACE_COOP + " J " +
				"		LEFT JOIN " + Constants.TABLE_MERCHANT_PLACE + " K " +
				"		ON J.place_id = K.id " +
				"	WHERE J.place_id = A.place_id " +
				"	AND J.status = " + Constants.COOP_PLACE_STATUS_AUTHORIZED + ") " +
				whereStr;
		result = DBConn.query(sql);
		if (result.first()) {
			dataMap.put("total_num", result.getString("total_num"));
		}
		
		sql = "" +
				" 	SELECT " +
				"		count(1) as waiting_num" +
				" 	FROM " + Constants.TABLE_MAINTAIN_ORDER + " A " +
				"		LEFT JOIN " + Constants.TABLE_CAR_INFO + " B " +
				"		ON A.license_number = B.license_number " +
				"		LEFT JOIN (" + Constants.TABLE_CUSTOMER + " C " +
				"			INNER JOIN " + Constants.TABLE_CUSTOMER_AUTHORIZATION + " E" +
				"			ON C.user_id = E.customer_id" +
				"			AND E.merchant_id = '" + StringEscapeUtils.escapeSql(merchantId) + "') " +
				"		ON B.customer_id = C.user_id " +
				"		LEFT JOIN " + Constants.TABLE_MERCHANT_PLACE + " D " +
				"		ON A.place_id = D.id " +
				"	WHERE '" + StringEscapeUtils.escapeSql(merchantId) + "' IN (" +
				" 	SELECT I.merchant_id " +
				" 	FROM " + Constants.TABLE_MAINTAIN_ORDER + " H " +
				"		LEFT JOIN " + Constants.TABLE_MERCHANT_PLACE + " I " +
				"		ON H.place_id = I.id " +
				" 	WHERE H.order_id = A.order_id " +
				" 	AND I.merchant_id is not null " +
				" UNION " +
				" 	SELECT merchant_id " +
				" 	FROM " + Constants.TABLE_MAINTAIN_ORDER +
				" 	WHERE order_id = A.order_id " +
				" UNION " +
				"	SELECT K.merchant_id " +
				"	FROM " + Constants.TABLE_MERCHANT_PLACE_COOP + " J " +
				"		LEFT JOIN " + Constants.TABLE_MERCHANT_PLACE + " K " +
				"		ON J.place_id = K.id " +
				"	WHERE J.place_id = A.place_id " +
				"	AND J.status = " + Constants.COOP_PLACE_STATUS_AUTHORIZED + ") " +
				"	AND A.status = " + Constants.MAINTAIN_STATUS_WAIT;
		result = DBConn.query(sql);
		if (result.first()) {
			dataMap.put("waiting_num", result.getString("waiting_num"));
		}
		
		return dataMap;
	}
	
	/**
	 * 添加项目快捷方式
	 */
	public static int addItemShortcut(String merchantId, String itemId, String shortcutName, String itemPhoto) throws Exception{
		
		String sql = "" +
				" INSERT INTO" +
				"	" + Constants.TABLE_ITEM_SHORTCUT + 
				"	(merchant_id, item_id, shortcut_name, item_photo) " +
				" VALUES " +
				"	('" + StringEscapeUtils.escapeSql(merchantId) + "', " +
				"	'" + StringEscapeUtils.escapeSql(itemId) + "', " + 
				"	'" + StringEscapeUtils.escapeSql(shortcutName) + "', " + 
				"	'" + StringEscapeUtils.escapeSql(itemPhoto) +  "')";
		
		DBConn.update(sql);
		
		return Constants.CODE_SUCCESS;
	}
	
	/**
	 * 修改项目快捷方式
	 */
	public static int modifyItemShortcut(String merchantId, String shortcutId, String itemId, String shortcutName, String itemPhoto) throws Exception{
		
		String sql = "" +
				" UPDATE " +
				"	" + Constants.TABLE_ITEM_SHORTCUT + 
				" SET " +
				"	item_id = '" + StringEscapeUtils.escapeSql(itemId) + "', " + 
				"	shortcut_name = '" + StringEscapeUtils.escapeSql(shortcutName) + "', " + 
				"	item_photo = '" + StringEscapeUtils.escapeSql(itemPhoto) +  "'" +
				" WHERE id = '" + StringEscapeUtils.escapeSql(shortcutId) + "' " +
				" AND merchant_id = '" + StringEscapeUtils.escapeSql(merchantId) + "'";
		
		DBConn.update(sql);
		
		return Constants.CODE_SUCCESS;
	}
	/**
	 * 删除项目快捷方式
	 */
	public static int deleteItemShortcut(String merchantId, String shortcutId) throws Exception{
		
		String sql = "" +
				" DELETE FROM " +
				"	" + Constants.TABLE_ITEM_SHORTCUT + 
				" WHERE id = '" + StringEscapeUtils.escapeSql(shortcutId) + "' " +
				" AND merchant_id = '" + StringEscapeUtils.escapeSql(merchantId) + "'";
		
		DBConn.update(sql);
		
		return Constants.CODE_SUCCESS;
	}
	
	/**
	 * 取得养护项目快捷方式
	 */
	public static Map<String, Object> getItemShortcut(String merchantId, int page, int pageSize) throws Exception{
		Map<String, Object> dataMap = new HashMap<String, Object>();
		List<ItemShortcut> ret = null;
		ItemShortcut shortcut = null;
		ResultSet result;
		String limitStr = "";
		
		if (page > 0 && pageSize > 0) {
			limitStr = "   LIMIT " + (page - 1) * pageSize + ","  + pageSize;
		}
		
		String sql = "" +
				" SELECT " +
				"	A.id, A.item_id, A.shortcut_name, A.item_photo, B.model, C.type_name, D.brand_name, E.period_name " +
				" FROM " + Constants.TABLE_ITEM_SHORTCUT + " A " +
				"	LEFT JOIN " + Constants.TABLE_ITEM + " B " +
				"	ON A.item_id = B.id " +
				"	LEFT JOIN " + Constants.TABLE_ITEM_TYPE + " C " +
				"	ON B.type_id = C.id " +
				"	LEFT JOIN " + Constants.TABLE_ITEM_BRAND + " D " +
				"	ON B.brand_id = D.id " +
				"	LEFT JOIN " + Constants.TABLE_ITEM_PERIOD + " E " +
				"	ON B.period_id = E.id " +
				" WHERE merchant_id = '" + StringEscapeUtils.escapeSql(merchantId) + "' " +
				" ORDER BY id ASC " +
				limitStr;
			
			
		result = DBConn.query(sql);
		
		if (result.first()) {
			ret = new ArrayList<ItemShortcut>();
			
			do {
				shortcut = new ItemShortcut();
				
				shortcut.setShortcutId(result.getString("id"));
				shortcut.setItemId(result.getString("item_id"));
				shortcut.setShortcutName(result.getString("shortcut_name"));
				shortcut.setItemPhoto(Configs.uploadPath.replaceAll("\\\\", "/") + result.getString("item_photo"));
				shortcut.setItemType(result.getString("type_name"));
				shortcut.setItemBrand(result.getString("brand_name"));
				shortcut.setItemPeriod(result.getString("period_name"));
				shortcut.setItemModel(result.getString("model"));
				
				ret.add(shortcut);
			} while (result.next());
		}
		
		dataMap.put("items", ret);
		
		sql = "" +
				" SELECT " +
				"	count(1) AS total_num " +
				" FROM " + Constants.TABLE_ITEM_SHORTCUT +
				" WHERE merchant_id = '" + StringEscapeUtils.escapeSql(merchantId) + "' ";
		
		result = DBConn.query(sql);
		if (result.first()) {
			dataMap.put("total_num", result.getString("total_num"));
		}
		return dataMap;
	}

	/**
	 * 保存消息
	 */
	public static boolean saveMessage(String messageId, String merchantId, String customerId, String title, String content) throws Exception{
		int result;
		String sql = "";
		
		if (Utils.empty(messageId)) {
			sql = "" +
					" INSERT INTO" +
					"	" + Constants.TABLE_MESSAGE_SEND + 
					"	(sender_id, receiver_id, type, msg_title, msg_content, status) " +
					" VALUES " +
					"	('" + StringEscapeUtils.escapeSql(merchantId) + "', " +
					"	'" + StringEscapeUtils.escapeSql(customerId) + "', " + 
					Constants.MSG_TYPE_MERCHANT_TO_CUSTOMER + ", " + 
					"	'" + StringEscapeUtils.escapeSql(title) + "', " + 
					"	'" + StringEscapeUtils.escapeSql(content) + "', " + 
					Constants.MSG_STATUS_WRITING + ")";
		} else {
			sql = "" +
					" UPDATE" +
					"	" + Constants.TABLE_MESSAGE_SEND + 
					" SET sender_id = '" + StringEscapeUtils.escapeSql(merchantId) + "', " +
					" 	receiver_id = '" + StringEscapeUtils.escapeSql(customerId) + "', " +
					" 	msg_title = '" + StringEscapeUtils.escapeSql(title) + "', " +
					" 	msg_content = '" + StringEscapeUtils.escapeSql(content) + "'" +
					" WHERE id = " + StringEscapeUtils.escapeSql(messageId);
		}
		result = DBConn.update(sql);
		
		return (result > 0);
	}
	
	/**
	 * 发送消息
	 */
	public static boolean sendMessage(String messageId, String merchantId, String customerId, String title, String content) throws Exception{
		int result;
		String sql = "";
		
		if (Utils.empty(messageId)) {
			sql = "" +
					" INSERT INTO" +
					"	" + Constants.TABLE_MESSAGE_SEND + 
					"	(sender_id, receiver_id, type, msg_title, msg_content, status) " +
					" VALUES " +
					"	('" + StringEscapeUtils.escapeSql(merchantId) + "', " +
					"	'" + StringEscapeUtils.escapeSql(customerId) + "', " + 
					Constants.MSG_TYPE_MERCHANT_TO_CUSTOMER + ", " + 
					"	'" + StringEscapeUtils.escapeSql(title) + "', " + 
					"	'" + StringEscapeUtils.escapeSql(content) + "', " + 
					Constants.MSG_STATUS_SENT + ")";
		} else {
			sql = "" +
					" UPDATE" +
					"	" + Constants.TABLE_MESSAGE_SEND + 
					" SET sender_id = '" + StringEscapeUtils.escapeSql(merchantId) + "', " +
					" 	receiver_id = '" + StringEscapeUtils.escapeSql(customerId) + "', " +
					" 	msg_title = '" + StringEscapeUtils.escapeSql(title) + "', " +
					" 	msg_content = '" + StringEscapeUtils.escapeSql(content) + "', " +
					" 	status = " + Constants.MSG_STATUS_SENT +
					" WHERE id = " + StringEscapeUtils.escapeSql(messageId);
		}
		DBConn.update(sql);
		
		sql = "" +
				" INSERT INTO" +
				"	" + Constants.TABLE_MESSAGE_RECEIVE + 
				"	(sender_id, receiver_id, type, msg_title, msg_content, receive_date, status) " +
				" VALUES " +
				"	('" + StringEscapeUtils.escapeSql(merchantId) + "', " +
				"	'" + StringEscapeUtils.escapeSql(customerId) + "', " + 
				Constants.MSG_TYPE_MERCHANT_TO_CUSTOMER + ", " + 
				"	'" + StringEscapeUtils.escapeSql(title) + "', " + 
				"	'" + StringEscapeUtils.escapeSql(content) + "', " + 
				"	'" + Utils.getStrTime(System.currentTimeMillis()) + "', " + 
				Constants.MSG_STATUS_UNREAD + ")";
		result = DBConn.update(sql);
		
		return (result > 0);
	}
	
	/**
	 * 取得消息
	 */
	public static Map<String, Object> getMessage(String merchantId, String type, int page, int pageSize) throws Exception{
		Map<String, Object> dataMap = new HashMap<String, Object>();
		List<Message> ret = null;
		ResultSet result;
		Message msg = null;
		String sql = "";
		String countSql = "";
		String limitStr = "";
		
		if (page > 0 && pageSize > 0) {
			limitStr = "   LIMIT " + (page - 1) * pageSize + ","  + pageSize;
		}
		
		if (type.equals(Constants.MSG_TYPE_RECEIVE + "")) {
			sql = "" +
					" SELECT " +
					"	id, sender_id as customer_id, msg_title, status, receive_date as date, B.name " +
					" FROM " + Constants.TABLE_MESSAGE_RECEIVE + " A " +
					" 	LEFT JOIN " + Constants.TABLE_CUSTOMER + " B " +
					"	ON A.receiver_id = B.user_id " +
					" WHERE type = " + Constants.MSG_TYPE_CUSTOMER_TO_MERCHANT +
					"	AND receiver_id = '" + StringEscapeUtils.escapeSql(merchantId) + "' " +
					" ORDER BY date DESC " +
					limitStr;
			
			countSql = "" +
					" SELECT count(1) as total_num " +
					" FROM " + Constants.TABLE_MESSAGE_RECEIVE +
					" WHERE type = " + Constants.MSG_TYPE_CUSTOMER_TO_MERCHANT +
					"	AND receiver_id = '" + StringEscapeUtils.escapeSql(merchantId) + "' ";
		} else if (type.equals(Constants.MSG_TYPE_DRAFT + "")) {
			sql = "" +
					" SELECT " +
					"	id, receiver_id as customer_id, msg_title, status, modify_date as date, B.name " +
					" FROM " + Constants.TABLE_MESSAGE_SEND + " A " +
					" 	LEFT JOIN " + Constants.TABLE_CUSTOMER + " B " +
					"	ON A.receiver_id = B.user_id " +
					" WHERE type = " + Constants.MSG_TYPE_MERCHANT_TO_CUSTOMER +
					"	AND sender_id = '" + StringEscapeUtils.escapeSql(merchantId) + "' " +
					"	AND status = " + Constants.MSG_STATUS_WRITING +
					" ORDER BY date DESC " +
					limitStr;
			
			countSql = "" +
					" SELECT count(1) as total_num " +
					" FROM " + Constants.TABLE_MESSAGE_SEND +
					" WHERE type = " + Constants.MSG_TYPE_MERCHANT_TO_CUSTOMER +
					"	AND sender_id = '" + StringEscapeUtils.escapeSql(merchantId) + "' " +
					"	AND status = " + Constants.MSG_STATUS_WRITING;
		} else if (type.equals(Constants.MSG_TYPE_SENT + "")) {
			sql = "" +
					" SELECT " +
					"	id, receiver_id as customer_id, msg_title, status, modify_date as date, B.name" +
					" FROM " + Constants.TABLE_MESSAGE_SEND + " A " +
					" 	LEFT JOIN " + Constants.TABLE_CUSTOMER + " B " +
					"	ON A.receiver_id = B.user_id " +
					" WHERE type = " + Constants.MSG_TYPE_MERCHANT_TO_CUSTOMER +
					"	AND sender_id = '" + StringEscapeUtils.escapeSql(merchantId) + "' " +
					"	AND status = " + Constants.MSG_STATUS_SENT +
					" ORDER BY date DESC " +
					limitStr;
			
			countSql = "" +
					" SELECT count(1) as total_num " +
					" FROM " + Constants.TABLE_MESSAGE_SEND +
					" WHERE type = " + Constants.MSG_TYPE_MERCHANT_TO_CUSTOMER +
					"	AND sender_id = '" + StringEscapeUtils.escapeSql(merchantId) + "' " +
					"	AND status = " + Constants.MSG_STATUS_SENT;
		}
		result = DBConn.query(sql);
		
		if (result.first()) {
			ret = new ArrayList<Message>();
			
			do {
				msg = new Message();
				msg.setMessageId(result.getString("id"));
				if (type.equals(Constants.MSG_TYPE_RECEIVE + "")) {
					msg.setSenderId(result.getString("customer_id"));
					msg.setSenderName(result.getString("name"));
				} else {
					msg.setReceiverId(result.getString("customer_id"));
					msg.setReceiverName(result.getString("name"));
				}
				msg.setTitle(result.getString("msg_title"));
				msg.setStatus(result.getString("status"));
				msg.setDate(result.getDate("date").toString() + " " + result.getTime("date").toString());
				
				ret.add(msg);
			} while (result.next());
		}
		
		dataMap.put("items", ret);
		
		result = DBConn.query(countSql);
		if (result.first()) {
			dataMap.put("total_num", result.getString("total_num"));
		}
		return dataMap;
	}
	
	/**
	 * 阅读消息
	 */
	public static boolean readMessage(String messageId, String merchantId) throws Exception{
		int result;
		
		String sql = "" +
					" UPDATE" +
					"	" + Constants.TABLE_MESSAGE_RECEIVE + 
					" SET status = " + Constants.MSG_STATUS_READ +
					" WHERE id = " + StringEscapeUtils.escapeSql(messageId) +
					" 	AND type = " + Constants.MSG_TYPE_CUSTOMER_TO_MERCHANT +
					"	AND receiver_id = '" + StringEscapeUtils.escapeSql(merchantId) + "'";

		result = DBConn.update(sql);
		
		return (result > 0);
	}
	
	/**
	 * 取得消息详细
	 */
	public static Message getMessageDetail(String messageId, String merchantId, String type) throws Exception{
		ResultSet result;
		String sql = "";
		Message msg = null;
		
		if (type.equals(Constants.MSG_TYPE_RECEIVE + "")) {
			sql = "" +
					" SELECT " +
					"	id, sender_id AS customer_id, msg_title, msg_content, status, receive_date as date, B.name " +
					" FROM " + Constants.TABLE_MESSAGE_RECEIVE + " A " +
					" 	LEFT JOIN " + Constants.TABLE_CUSTOMER + " B " +
					"	ON A.receiver_id = B.user_id " +
					" WHERE type = " + Constants.MSG_TYPE_CUSTOMER_TO_MERCHANT +
					"	AND receiver_id = '" + StringEscapeUtils.escapeSql(merchantId) + "' " +
					"	AND id = " + StringEscapeUtils.escapeSql(messageId) +
					" ORDER BY date DESC ";
		} else if (type.equals(Constants.MSG_TYPE_DRAFT + "")) {
			sql = "" +
					" SELECT " +
					"	id, receiver_id AS customer_id, msg_title, msg_content, status, modify_date as date, B.name " +
					" FROM " + Constants.TABLE_MESSAGE_SEND + " A " +
					" 	LEFT JOIN " + Constants.TABLE_CUSTOMER + " B " +
					"	ON A.receiver_id = B.user_id " +
					" WHERE type = " + Constants.MSG_TYPE_MERCHANT_TO_CUSTOMER +
					"	AND sender_id = '" + StringEscapeUtils.escapeSql(merchantId) + "' " +
					"	AND id = " + StringEscapeUtils.escapeSql(messageId) +
					"	AND status = " + Constants.MSG_STATUS_WRITING;
		} else if (type.equals(Constants.MSG_TYPE_SENT + "")) {
			sql = "" +
					" SELECT " +
					"	id, receiver_id AS customer_id, msg_title, msg_content, status, modify_date as date, B.name" +
					" FROM " + Constants.TABLE_MESSAGE_SEND + " A " +
					" 	LEFT JOIN " + Constants.TABLE_CUSTOMER + " B " +
					"	ON A.receiver_id = B.user_id " +
					" WHERE type = " + Constants.MSG_TYPE_MERCHANT_TO_CUSTOMER +
					"	AND sender_id = '" + StringEscapeUtils.escapeSql(merchantId) + "' " +
					"	AND id = " + StringEscapeUtils.escapeSql(messageId) +
					"	AND status = " + Constants.MSG_STATUS_SENT;
		}
		result = DBConn.query(sql);
		
		if (result.first()) {
			msg = new Message();
			msg.setMessageId(result.getString("id"));
			if (type.equals(Constants.MSG_TYPE_RECEIVE + "")) {
				msg.setSenderId(result.getString("customer_id"));
				msg.setSenderName(result.getString("name"));
			} else {
				msg.setReceiverId(result.getString("customer_id"));
				msg.setReceiverName(result.getString("name"));
			}
			msg.setTitle(result.getString("msg_title"));
			msg.setContent(result.getString("msg_content"));
			msg.setStatus(result.getString("status"));
			msg.setDate(result.getDate("date").toString() + " " + result.getTime("date").toString());
		}
		
		return msg;
	}
	
	/**
	 * 删除消息
	 */
	public static boolean deleteMessage(String messageId, String merchantId, String type) throws Exception{
		String sql = "";
		
		if (type.equals(Constants.MSG_TYPE_RECEIVE + "")) {
			sql = "" +
					" DELETE FROM " + Constants.TABLE_MESSAGE_RECEIVE + 
					" WHERE type = " + Constants.MSG_TYPE_CUSTOMER_TO_MERCHANT +
					"	AND receiver_id = '" + StringEscapeUtils.escapeSql(merchantId) + "' " +
					"	AND id = " + StringEscapeUtils.escapeSql(messageId);
		} else if (type.equals(Constants.MSG_TYPE_DRAFT + "")) {
			sql = "" +
					" DELETE FROM " + Constants.TABLE_MESSAGE_SEND + 
					" WHERE type = " + Constants.MSG_TYPE_MERCHANT_TO_CUSTOMER +
					"	AND sender_id = '" + StringEscapeUtils.escapeSql(merchantId) + "' " +
					"	AND id = " + StringEscapeUtils.escapeSql(messageId) +
					"	AND status = " + Constants.MSG_STATUS_WRITING;
		} else if (type.equals(Constants.MSG_TYPE_SENT + "")) {
			sql = "" +
					" DELETE FROM " + Constants.TABLE_MESSAGE_SEND + 
					" WHERE type = " + Constants.MSG_TYPE_MERCHANT_TO_CUSTOMER +
					"	AND sender_id = '" + StringEscapeUtils.escapeSql(merchantId) + "' " +
					"	AND id = " + StringEscapeUtils.escapeSql(messageId) +
					"	AND status = " + Constants.MSG_STATUS_SENT;
		}
		DBConn.update(sql);
		
		return true;
	}
	
	/**
	 * 取得可用经营地址列表
	 */
	public static List<MerchantPlace> getAvailablePlaces(String merchantId) throws Exception{
		List<MerchantPlace> places = null;
		MerchantPlace place = null;
		ResultSet result;
		
		String sql = "" +
				" 	SELECT " +
				"		id, place_name, address" +
				" 	FROM " + Constants.TABLE_MERCHANT_PLACE +
				"	WHERE status = " + Constants.PLACE_STATUS_VERIFIED +
				"	AND merchant_id != '" + StringEscapeUtils.escapeSql(merchantId) + "'" +
				"	AND id NOT IN (" +
				"		SELECT place_id" +
				"		FROM " + Constants.TABLE_MERCHANT_PLACE_COOP +
				"		WHERE merchant_id = '" + StringEscapeUtils.escapeSql(merchantId) + "')";
		result = DBConn.query(sql);
		
		if (result.first()) {
			places = new ArrayList<MerchantPlace>();
			do {
				place = new MerchantPlace();
				place.setId(result.getString("id"));
				place.setPlaceName(result.getString("place_name"));
				place.setAddress(result.getString("address"));
				
				places.add(place);
			} while (result.next());
		}
		
		return places;
	}
}
